define erp='erp_mli2.';
define outils='outils_mli2.';

--create table se_PAR_MODPAIECH as (select * from &erp.PAR_MODPAIECH where 1=2);


DELETE FROM  se_PAR_MODPAIECH ;



INSERT INTO se_PAR_MODPAIECH

SELECT id modpaiechcalc_cod,
       id modpai_cod,
       NULL modreg_cod,
       0 enreg_id,
       1 modpaiech_rang,
       100 modpaiech_pourc,
       NULL calcautoaco_tx
FROM
 ( SELECT p.*,mp.modpai,mr.modreg,po.pourc
   FROM uv_cpaiements p, uv_cpaiements_modpai  mp, uv_cpaiements_modreg mr, uv_cpaiements_pourc po
   WHERE p.id=mp.id(+)
     AND mr.id(+)=mp.id AND mr.RANGMULTI2(+) =mp.RANGMULTI2
     AND po.id(+)=mp.id AND po.RANGMULTI2(+) =mp.RANGMULTI2
     AND mp.modpai IS null
 --ORDER BY p.id
) ;

INSERT INTO se_PAR_MODPAIECH

(SELECT MODPAI modpaiechcalc_cod,
       id modpai_cod,
       MODREG modreg_cod,
       0 enreg_id,
       Min(RANGMULTI2+1) modpaiech_rang,
       Sum(POURC/100) modpaiech_pourc,
       NULL calcautoaco_tx
FROM
 ( SELECT p.*,mp.modpai,mr.modreg,po.pourc,po.RANGMULTI2
   FROM uv_cpaiements p, uv_cpaiements_modpai  mp, uv_cpaiements_modreg mr, uv_cpaiements_pourc po
   WHERE p.id=mp.id(+)
     AND mr.id(+)=mp.id AND mr.RANGMULTI2(+) =mp.RANGMULTI2
     AND po.id(+)=mp.id AND po.RANGMULTI2(+) =mp.RANGMULTI2
     AND mp.modpai IS not null
 --ORDER BY p.id
)
GROUP BY MODREG,MODPAI,id ) ;


 UPDATE se_PAR_MODPAIECH tf
 SET enreg_id = (select enreg_id FROM &erp.PAR_MODPAIECH f WHERE f.modpaiechcalc_cod=tf.modpaiechcalc_cod AND f.modpai_cod=tf.modpai_cod )
 WHERE EXISTS (SELECT 1 FROM &erp.PAR_MODPAIECH f WHERE f.modpaiechcalc_cod=tf.modpaiechcalc_cod AND f.modpai_cod=tf.modpai_cod);

 UPDATE se_PAR_MODPAIECH SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 CALL uvse_synchroverserp('modpai_cod@modpaiechcalc_cod','PAR_MODPAIECH','O') ;



 commit;

select * from (
SELECT &outils.stragg(source) source,modpaiechcalc_cod, modpai_cod, modreg_cod, enreg_id, modpaiech_rang, modpaiech_pourc, calcautoaco_tx
FROM (
  SELECT 'uv' source ,g.* FROM se_PAR_MODPAIECH g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.PAR_MODPAIECH g
  )
GROUP BY modpaiechcalc_cod, modpai_cod, modreg_cod, enreg_id, modpaiech_rang, modpaiech_pourc, calcautoaco_tx
HAVING Count(1)!=2) r
ORDER BY modpaiechcalc_cod;